package cn.com.vastbase.jpa.dao;

import cn.com.vastbase.jpa.dataobject.UserDO;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.Collection;
import java.util.List;
import java.util.Map;

@Repository
public interface NativeQueryRepository extends JpaRepository<UserDO, Integer> {

    @Query(value = "select * from (SELECT " +
            "  ( CASE WHEN media.media_type = 6 THEN 0 ELSE 1 END ) mediatype," +
            "( CASE WHEN media.operation_permission_id IS NULL THEN 0 ELSE ( SELECT LEVEL FROM operation_permission WHERE id = media.operation_permission_id ) END ) level," +
            "media.*  " +
            "FROM MIMS_MEDIA media " +
            "LEFT JOIN MEDIA_VIDEO_ATTRIBUTE video ON video.media_uuid = media.uuid " +
            "LEFT JOIN MEDIA_AUDIO_ATTRIBUTE audio ON audio.media_id = media.id " +
            "LEFT JOIN MEDIA_VIDEO_STREAM_ATTRIBUTE videoStream ON videoStream.media_id = media.id " +
            "LEFT JOIN MEDIA_AUDIO_STREAM_ATTRIBUTE audioStream ON audioStream.media_id = media.id " +
            "WHERE delete_status=0  " +
            "AND (IF(?1 is null, true, media.lib_id = ?1)) " +
            "AND IF(?2 is null, true, media.folder_id = ?2) " +
            "AND(" +
            "( media.media_type = 6 AND media.id IN ?18) or(media.media_type != 6 " +
            "AND IF(?3 is null, true, media.media_type = ?3) " +
            "AND  IF(?4 is null, true, media.name LIKE CONCAT('%',ifnull( ?4 , 'null' ),'%')) " +
            "AND IF(?5 is null, true, media.create_time >= ?5) " +
            "AND IF(?6 is null, true, media.create_time <= ?6) " +
            "AND IF(?7 is null, true, media.source_type = ?7) " +
            "AND IF(?8 is null, true, video.resolution =?8) " +
            "AND IF(?9 is null, true, media.duration >= ?9) " +
            "AND IF(?10 is null, true, media.duration <= ?10) " +
            "AND IF(?11 = 1, video.resolution_height < video.resolution_width , true) " +
            "AND IF(?11 = 2, video.resolution_height > video.resolution_width , true) " +
            "AND IF(?11 = 3, video.resolution_height = video.resolution_width , true) " +
            "AND IF(?11 = 4, video.resolution_height * 2  = video.resolution_width , true) " +
            "AND ( media.review_status in ?12 or (media.review_status in (1)  and  media.author_id =  ?13) )  " +
            "AND IF(?14 is null, true, media.delete_status = ?14) " +
            "AND IF(?15 is null, true, audioStream.channel_num = ?15) " +
            "AND IF(?16 is null, true, audioStream.channel_num = ?16) " +
            "AND IF(?17 is null, true, (videoStream.stream_type = ?17 or audioStream.stream_type = ?17))" +
            "AND IF(?20 is null, true, media.material_media_type = ?20) " +
            "AND IF(?21 is null OR ?21='', true, media.material_category = ?21) " +
            "AND (media.addition  is NULL OR media.addition NOT LIKE CONCAT('%','\"是否显示\":\"false\"','%') ) " +
            ")" +
            ") " +
            " ) media where (level <=?19 or level is null) ",
            countQuery = "select count(*) from (SELECT " +
                    "( CASE WHEN media.operation_permission_id IS NULL THEN 0 ELSE ( SELECT LEVEL FROM operation_permission WHERE id = media.operation_permission_id ) END ) level " +
                    "FROM MIMS_MEDIA media " +
                    "LEFT JOIN MEDIA_VIDEO_ATTRIBUTE video ON video.media_uuid = media.uuid " +
                    "LEFT JOIN MEDIA_AUDIO_ATTRIBUTE audio ON audio.media_id = media.id " +
                    "LEFT JOIN MEDIA_VIDEO_STREAM_ATTRIBUTE videoStream ON videoStream.media_id = media.id " +
                    "LEFT JOIN MEDIA_AUDIO_STREAM_ATTRIBUTE audioStream ON audioStream.media_id = media.id " +
                    "WHERE delete_status=0  " +
                    "AND (IF(?1 is null, true, media.lib_id = ?1)) " +
                    "AND IF(?2 is null, true, media.folder_id = ?2) " +
                    "AND (" +
                    "( media.media_type = 6 AND media.id IN ?18) or( media.media_type != 6 " +
                    "AND IF(?3 is null, true, media.media_type = ?3) " +
                    "AND  IF(?4 is null, true, media.name LIKE CONCAT('%', ?4,'%')) " +
                    "AND IF(?5 is null, true, media.create_time >= ?5) " +
                    "AND IF(?6 is null, true, media.create_time <= ?6) " +
                    "AND IF(?7 is null, true, media.source_type = ?7) " +
                    "AND IF(?8 is null, true, video.resolution =?8) " +
                    "AND IF(?9 is null, true, media.duration >= ?9) " +
                    "AND IF(?10 is null, true, media.duration <= ?10) " +
                    "AND IF(?11 = 1, video.resolution_height < video.resolution_width , true) " +
                    "AND IF(?11 = 2, video.resolution_height > video.resolution_width , true) " +
                    "AND IF(?11 = 3, video.resolution_height = video.resolution_width , true) " +
                    "AND IF(?11 = 4, video.resolution_height * 2  = video.resolution_width , true) " +
                    "AND ( media.review_status in ?12 or (media.review_status in (1)  and  media.author_id =  ?13) )  " +
                    "AND IF(?14 is null, true, media.delete_status = ?14) " +
                    "AND IF(?15 is null, true, audioStream.channel_num = ?15) " +
                    "AND IF(?16 is null, true, audioStream.channel_num = ?16) " +
                    "AND IF(?17 is null, true, (videoStream.stream_type = ?17 or audioStream.stream_type = ?17)) " +
                    "AND IF(?20 is null, true, media.material_media_type = ?20) " +
                    "AND IF(?21 is null OR ?21='', true, media.material_category = ?21) " +
                    "AND (media.addition  is NULL OR media.addition NOT LIKE CONCAT('%','\"是否显示\":\"false\"','%') ) " +
                    ") " +
                    ") " +
                    ")a where (level <=?19 or level is null) ", nativeQuery = true)
    Page<Map<String, Object>> findMediaPOByConditionPublic(Long libido,
                                                           Long folderId,
                                                           Integer mediaType,
                                                           String name,
                                                           String startTime,
                                                           String endTime,
                                                           Integer sourceType,
                                                           Integer resolution,
                                                           Long durationLeast,
                                                           Long durationMost,
                                                           int composition,
                                                           Collection<Integer> reviewStatus,
                                                           Long userId,
                                                           Integer deleteStatus,
                                                           Long channelNum,
                                                           Long sampleRate,
                                                           Integer streamType,
                                                           List<Long> ids,
                                                           int level,
                                                           Integer materialMediaTypeOrdinal,
                                                           String materialCategory,
                                                           Pageable page);
}
